package com.loveyukari.dao.impl;

import com.loveyukari.dao.RoomGoldRankDao;
import com.loveyukari.model.RoomGoldRank;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class JdbcRoomGoldRankDao extends BaseDao implements RoomGoldRankDao {

    private RowMapper<RoomGoldRank> roomGoldRankRowMapper = new RowMapper<RoomGoldRank>() {
        @Override
        public RoomGoldRank mapRow(ResultSet resultSet, int i) throws SQLException {
            RoomGoldRank roomGoldRank = new RoomGoldRank();
            roomGoldRank.setGroupId(resultSet.getInt("group_id"));
            roomGoldRank.setRank(resultSet.getInt("rank"));
            roomGoldRank.setUserId(resultSet.getInt("user_id"));
            roomGoldRank.setGold(resultSet.getInt("gold"));
            roomGoldRank.setId(resultSet.getInt("id"));
            return roomGoldRank;
        }
    };

    @Override
    public List<RoomGoldRank> selectListByGroupId(int groupId) {
        String sql = "select * from room_gold_rank where group_id = ? order by rank";
        List<RoomGoldRank> list = this.getJdbcTemplate().query(sql, roomGoldRankRowMapper, groupId);
        return list;
    }

    @Override
    public void insertRoomGoldRank(RoomGoldRank roomGoldRank) {
        String sql = "insert into room_gold_rank (user_id,gold,rank,group_id) values(?,?,?,?)";
        this.getJdbcTemplate().update(sql,roomGoldRank.getUserId(),roomGoldRank.getGold(),roomGoldRank.getRank(),roomGoldRank.getGroupId());
    }

    @Override
    public void updateRoomGoldRank(RoomGoldRank roomGoldRank) {
        String sql = "update room_gold_rank set rank = ? , user_id = ? , gold = ? where id = ?";
        this.getJdbcTemplate().update(sql,roomGoldRank.getRank(),roomGoldRank.getUserId(),roomGoldRank.getGold(),roomGoldRank.getId());
    }

    @Override
    public int selectLastGroup() {
        String sql = "select id from room_gold_rank_group order by id desc limit 1";
        List<Integer> list = this.getJdbcTemplate().queryForList(sql, Integer.class);
        if (list.isEmpty()){
            return 0;
        } else {
            return list.get(0);
        }
    }

    @Override
    public void insertGroup() {
        String sql = "insert into room_gold_rank_group (create_time) values(now())";
        this.getJdbcTemplate().update(sql);
    }

    @Override
    public List<RoomGoldRank> selectTmp() {
        String sql = "select user_id,sum(gold) as sg from room_gold_rank where group_id in (select id from (select * from room_gold_rank_group order by id desc limit 2) as a) group by user_id order by sum(gold)  desc limit 10";
        List<RoomGoldRank> list = this.getJdbcTemplate().query(sql, new ResultSetExtractor<List<RoomGoldRank>>() {
            @Override
            public List<RoomGoldRank> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                ArrayList<RoomGoldRank> list = new ArrayList<>();
                int rank = 1;
                while(resultSet.next()){
                    RoomGoldRank roomGoldRank = new RoomGoldRank();
                    roomGoldRank.setRank(rank++);
                    roomGoldRank.setUserId(resultSet.getInt("user_id"));
                    roomGoldRank.setGold(resultSet.getInt("sg"));
                    list.add(roomGoldRank);
                }
                return list;
            }
        });
        return list;
    }
}
